package org.jeecgframework.web.service.impl;

import com.mybatis.ext.WiseCoreService;
import org.jeecgframework.core.common.model.json.AjaxJson;
import org.jeecgframework.core.common.model.json.DataGrid;
import org.jeecgframework.core.common.service.impl.CommonServiceImpl;
import org.jeecgframework.core.constant.Globals;
import org.jeecgframework.core.util.ResourceUtil;
import org.jeecgframework.core.util.StringUtil;
import org.jeecgframework.core.util.oConvertUtils;
import org.jeecgframework.web.service.CbsDelinerServiceI;
import org.jeecgframework.web.service.CbsServiceAssignListServiceI;
import org.jeecgframework.web.service.CbsServiceLogServiceI;
import org.jeecgframework.web.service.ZyFileLogServiceI;
import org.jeecgframework.web.system.pojo.base.*;
import org.jeecgframework.web.system.service.SystemService;
import org.jeecgframework.web.util.SendMailUtil;
import org.jeecgframework.web.util.StaticValue;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.transaction.interceptor.TransactionAspectSupport;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.Serializable;
import java.text.SimpleDateFormat;
import java.util.*;

@Service("cbsServiceAssignListService")
@Transactional
public class CbsServiceAssignListServiceImpl extends CommonServiceImpl implements CbsServiceAssignListServiceI {
    @Autowired
    private SystemService systemService;
    @Autowired
    private ZyFileLogServiceI zyFileLogServiceI;
    @Autowired
    private WiseCoreService wiseCoreService;
    @Autowired
    private CbsDelinerServiceI cbsDelinerService;
    @Autowired
    private CbsServiceLogServiceI cbsServiceLogServiceI;

    public <T> void delete(T entity) {
        super.delete(entity);
        //执行删除操作配置的sql增强
        this.doDelSql((CbsServiceAssignListEntity) entity);
    }

    public <T> Serializable save(T entity) {
        Serializable t = super.save(entity);
        //执行新增操作配置的sql增强
        this.doAddSql((CbsServiceAssignListEntity) entity);
        return t;
    }

    public <T> void saveOrUpdate(T entity) {
        super.saveOrUpdate(entity);
        //执行更新操作配置的sql增强
        this.doUpdateSql((CbsServiceAssignListEntity) entity);
    }

    /**
     * 默认按钮-sql增强-新增操作
     *
     * @param
     * @return
     */
    public boolean doAddSql(CbsServiceAssignListEntity t) {
        return true;
    }

    /**
     * 默认按钮-sql增强-更新操作
     *
     * @param
     * @return
     */
    public boolean doUpdateSql(CbsServiceAssignListEntity t) {
        return true;
    }

    /**
     * 默认按钮-sql增强-删除操作
     *
     * @param
     * @return
     */
    public boolean doDelSql(CbsServiceAssignListEntity t) {
        return true;
    }

    @Override
    public List<Map<String, Object>> queryUsersByOrgCodeAndRoleCodeTp(List<String> orgCode, String roleCode) {
        if (!StringUtil.isNotEmpty(orgCode) || !StringUtil.isNotEmpty(roleCode)) {
            return null;
        }
        String code = "";
        for (String s : orgCode) {
            if (s.equals(orgCode.get(orgCode.size() - 1))) {
                code += s;
            } else {
                code += s + ",";
            }
        }

        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT");
        sql.append(" ct.id AS id, ct.`name` as realname, ct.cellphone1 as cellphone, ct.email, ct.state,");
        sql.append(" ro.rolecode AS rolename, ro.id AS roid, ");
        sql.append(" dp.id AS dpid, dp.org_code AS CODE ");
        sql.append(" FROM");
        sql.append(" t_s_base_user bu");
        sql.append(" LEFT JOIN zy_contacts ct on ct.base_id = bu.ID ");
        sql.append(" LEFT JOIN zy_engineer eg on eg.ts_base_user_id = bu.ID ");
        sql.append(" LEFT JOIN t_s_user_org uo on uo.user_id = bu.ID");
        sql.append(" LEFT JOIN t_s_depart dp on dp.ID = uo.org_id");
        sql.append(" LEFT JOIN t_s_role_user ru on ru.userid = bu.ID");
        sql.append(" LEFT JOIN t_s_role ro on ro.ID = ru.roleid");
        sql.append(" WHERE");
        sql.append(" dp.org_code in (" + code + ") ");
        sql.append(" AND dp.logic_delete = 0 ");
        sql.append(" AND ro.rolecode = '").append(roleCode).append("' ");
//        System.out.println(sql);
        return this.findForJdbc(sql.toString());
    }

    @Override
    public List<Map<String, Object>> queryUsersByOrgCodeAndRoleCode(List<String> orgCode, String roleCode) {
        if (!StringUtil.isNotEmpty(orgCode) || !StringUtil.isNotEmpty(roleCode)) {
            return null;
        }
        String code = "";
        for (String s : orgCode) {
            if (s.equals(orgCode.get(orgCode.size() - 1))) {
                code += s;
            } else {
                code += s + ",";
            }
        }
        TSUser user = ResourceUtil.getSessionUserName();
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT ");
        sql.append(" eg.id AS id, bu.realname as realname, IFNULL(eg.cellphone,''), eg.email, eg.state,");
        sql.append(" ro.rolecode AS rolename, ro.id AS roid, ");
        sql.append(" dp.id AS dpid, dp.org_code AS CODE ");
        sql.append(" FROM ");
        sql.append(" t_s_base_user bu ");
        sql.append(" LEFT JOIN zy_engineer eg on eg.ts_base_user_id = bu.ID ");
        sql.append(" LEFT JOIN t_s_user_org uo on uo.user_id = bu.ID ");
        sql.append(" LEFT JOIN t_s_depart dp on dp.ID = uo.org_id ");
        sql.append(" LEFT JOIN t_s_role_user ru on ru.userid = bu.ID ");
        sql.append(" LEFT JOIN t_s_role ro on ro.ID = ru.roleid ");
        sql.append(" WHERE ");
        sql.append(" eg.ts_base_user_id = '").append(user.getId()).append("' ");//区域工程师可以分配给自己任务，所以这里根据当前用户（当前登录的是区域工程师）查询出自己
        sql.append(" AND eg.is_delete = '0' ");//只查询未逻辑删除的
        sql.append(" UNION ");
        sql.append(" SELECT");
        sql.append(" eg.id AS id, bu.realname as realname, IFNULL(eg.cellphone,''), eg.email, eg.state, ");
        sql.append(" ro.rolecode AS rolename, ro.id AS roid, ");
        sql.append(" dp.id AS dpid, dp.org_code AS CODE ");
        sql.append(" FROM");
        sql.append(" t_s_base_user bu");
        sql.append(" LEFT JOIN zy_engineer eg on eg.ts_base_user_id = bu.ID ");
        sql.append(" LEFT JOIN t_s_user_org uo on uo.user_id = bu.ID");
        sql.append(" LEFT JOIN t_s_depart dp on dp.ID = uo.org_id");
        sql.append(" LEFT JOIN t_s_role_user ru on ru.userid = bu.ID");
        sql.append(" LEFT JOIN t_s_role ro on ro.ID = ru.roleid");
        sql.append(" WHERE");
        sql.append(" dp.org_code in (" + code + ") ");
        sql.append(" AND dp.logic_delete = 0 ");
        sql.append(" AND ro.rolecode = '").append(roleCode).append("' ");
//        System.out.println(sql);
        return this.findForJdbc(sql.toString());

    }

    @Override
    public AjaxJson SendSingleServiceBusiness(String cbsServiceId) {
        AjaxJson ajaxJson = new AjaxJson();
        boolean flag = true;
        String msg="OK";
        /*是否特批”如果为是，但是没有上传特批文件，不可派单*/
        for (String s : cbsServiceId.split(",")) {
            if (flag) {
                String sql1 = "SELECT `status`, speed_status ,special ,service_type_1,service_type_2 FROM cbs_service WHERE id = '" + s + "'";
                List<Map<String, Object>> s1 = systemService.findForJdbc(sql1.toString());
                if (s1.size() > 0) {
                    String special = String.valueOf(s1.get(0).get("special"));
                    if (special.equals("1")) {
                        String sql = "SELECT COUNT(id) as count from zy_file_log WHERE business_child_type='" + StaticValue.Business_File_Type_B + "' and business_key='" + s + "'";
                        List<Map<String, Object>> i = systemService.findForJdbc(sql);
                        if (i.size() > 0 ) {
                            Long m = Long.parseLong(i.get(0).get("count").toString());
                            if(m==0){
                                flag = false;
                                msg="特批文件不能为空！";
                                break;
                            }

                        }
                    }
                    String speed = String.valueOf(s1.get(0).get("speed_status"));
                    String status = String.valueOf(s1.get(0).get("status"));
                    if (status.equals(StaticValue.clz)) {
                        if (!speed.equals(StaticValue.tfpdsf) || !speed.equals(StaticValue.dsfyjj)) {
                            flag = false;
                            msg="该状态下不能发生派单操作！";
                            break;
                        }
                    }
                    if(speed.equals(StaticValue.gcsssz)||speed.equals(StaticValue.fwywc)||status.equals(StaticValue.ywc)||status.equals(StaticValue.yqx)){
                        msg="该状态下不能发生派单操作！";
                        flag = false;
                        break;
                    }

                    if(s1.get(0).get("service_type_1")==null||s1.get(0).get("service_type_1")==""||s1.get(0).get("service_type_2")==null||s1.get(0).get("service_type_2")==""){
                        msg="服务单类型不能为空！";
                        flag = false;
                        break;
                    }
                }
            }
        }
        ajaxJson.setSuccess(flag);
        ajaxJson.setMsg(msg);
        return ajaxJson;
    }

    @Override
    public List<Map<String, Object>> getOrgByUserId(String userId, String OrgName) {
        StringBuffer af = new StringBuffer();
        af.append("SELECT\n");
        af.append("	d.org_code AS CODE\n");
        af.append("FROM\n");
        af.append("	t_s_base_user b\n");
        af.append("LEFT JOIN zy_contacts c ON c.base_id = b.id\n");
        af.append("LEFT JOIN t_s_user_org uo ON uo.user_id = b.id\n");
        af.append("LEFT JOIN t_s_depart d ON d.id = uo.org_id\n");
        af.append("WHERE\n");
        af.append("	b.id = '").append(userId).append("'");
        af.append("AND d.org_type = '").append(OrgName).append("'");
        return this.findForJdbc(af.toString());
    }

    @Override
    public List<Map<String, Object>> getDetailOfEng(String ServiceId, String UserId) {
        StringBuffer sf = new StringBuffer();
        sf.append("SELECT\n");
        sf.append("s.id as id,\n");
        sf.append("s.`code` AS `code`,\n");
        sf.append("s.speed_status AS `speedstatus`,\n");
        sf.append("s.HANDLE_DES AS `handleDes`,\n");
        sf.append("\tCASE s.speed_status\n");
        sf.append("WHEN '0' THEN\n");
        sf.append("\t'HD待分派'\n");
        sf.append("WHEN '1' THEN\n");
        sf.append("\t'第三方已拒绝'\n");
        sf.append("WHEN '2' THEN\n");
        sf.append("\t'已分派第三方'\n");
        sf.append("WHEN '3' THEN\n");
        sf.append("\t'第三方已分配'\n");
        sf.append("WHEN '4' THEN\n");
        sf.append("\t'区域工程师已分配'\n");
        sf.append("WHEN '5' THEN\n");
        sf.append("\t'工程师已接受'\n");
        sf.append("WHEN '6' THEN\n");
        sf.append("\t'工程师实施中'\n");
        sf.append("WHEN '7' THEN\n");
        sf.append("\t'服务已完成'\n");
        sf.append("END AS speed,");
        sf.append("	l.assign_des AS assign_des,\n");
        sf.append("	t1.`name` AS t1Name,\n");
        sf.append("	t2.`name` AS t2Name,\n");
        sf.append("	IFNULL(\n");
        sf.append("		DATE_FORMAT(\n");
        sf.append("			s.assign_time,\n");
        sf.append("			'%Y-%m-%d %H:%i:%s'\n");
        sf.append("		),\n");
        sf.append("		''\n");
        sf.append("	) AS assignTime,\n");
        sf.append("\tIFNULL(\n");
        sf.append("\t\tconcat(\n");
        sf.append("\n");
        sf.append("\t\t\tIF (\n");
        sf.append("\t\t\t\ts.`SPEED_STATUS` = 7,\n");
        sf.append("\t\t\t\tTIMESTAMPDIFF(\n");
        sf.append("\t\t\t\t\tHOUR,\n");
        sf.append("\t\t\t\t\ts.assign_time,\n");
        sf.append("\t\t\t\t\ts.close_time\n");
        sf.append("\t\t\t\t),\n");
        sf.append("\t\t\t\tTIMESTAMPDIFF(HOUR, s.assign_time, NOW())\n");
        sf.append("\t\t\t),\n");
        sf.append("\t\t\t'小时'\n");
        sf.append("\t\t),\n");
        sf.append("\t\t''\n");
        sf.append("\t) AS dealWithTiem,\n");
        sf.append("	s.`level` AS `level`,\n");
        sf.append("	s.`description` AS description,\n");
        sf.append("	s.contacts_phone AS phone,\n");
        sf.append("	s.email AS email,\n");
        sf.append("	s.contacts_name AS `name`,\n");
        sf.append("	s.`zy_cinema_name` AS ciName,\n");
        sf.append("	s.address AS address,\n");
        sf.append("	s.type_intheater_name AS type_intheater_name,\n");
        sf.append("	IFNULL(s.sn,s.sn_server) AS sn,\n");
        sf.append("	ay.TYPE_DESC AS type_desc,\n");
        sf.append("CASE\n" +
                "WHEN TIMESTAMPDIFF(\n" +
                "\tDAY,\n" +
                "\tNOW(),\n" +
                "\teq.repair_end_date\n" +
                ") < 0 THEN\n" +
                "\t'保外'\n" +
                "WHEN TIMESTAMPDIFF(\n" +
                "\tDAY,\n" +
                "\tNOW(),\n" +
                "\teq.repair_end_date\n" +
                ") > 0 THEN\n" +
                "\t'保内'\n" +
                "ELSE\n" +
                "\t''\n" +
                "END  AS inReparirDateVal,\n");
        sf.append("IF (\n");
        sf.append("	eq.LASER_RECONSTRUCTION = 1,\n");
        sf.append("	'是',\n");
        sf.append("	'否'\n");
        sf.append(") AS laser,\n");
        sf.append(" IFNULL(\n");
        sf.append("	DATE_FORMAT(\n");
        sf.append("		s.order_time,\n");
        sf.append("		'%Y-%m-%d'\n");
        sf.append("	),\n");
        sf.append("	''\n");
        sf.append(") AS ordertime,\n");
        sf.append(" IFNULL(\n");
        sf.append("	DATE_FORMAT(\n");
        sf.append("		s.order_time_agin,\n");
        sf.append("		'%Y-%m-%d'\n");
        sf.append("	),\n");
        sf.append("	''\n");
        sf.append(") AS orderTimeAgin,\n");
        sf.append(" s.remarks as remarks \n");
        sf.append("FROM\n");
        sf.append("	CBS_SERVICE_ASSIGN_LIST l\n");
        sf.append("LEFT JOIN cbs_service s ON l.service_id = s.id\n");
        sf.append("LEFT JOIN cbs_service_type t1 ON s.service_type_1 = t1.id\n");
        sf.append("LEFT JOIN cbs_service_type t2 ON s.service_type_2 = t2.id\n");
        sf.append("LEFT JOIN zy_contacts c ON c.id = s.zy_contacts_id\n");
        sf.append("LEFT JOIN zy_cinema ci ON ci.id = s.zy_cinema_id\n");
        sf.append("LEFT JOIN zy_equipment_spare eq ON eq.id = s.zy_equipment_spare_id\n");
        sf.append("LEFT JOIN zy_equipment_type ay ON ay.id = eq.type\n");
        sf.append(" WHERE\n");
        sf.append("	s.id = '").append(ServiceId).append("' ");
        sf.append("AND assign_touser = '").append(UserId).append("'");
        return this.findForJdbc(sf.toString());

    }

    @Override
    public List<Map<String, Object>> getDetailOfService(String ServiceId, String UserId) {
        StringBuffer sf = new StringBuffer();
        sf.append("SELECT\n");
        sf.append("s.id as id,\n");
        sf.append("s.`code` AS `code`,\n");
        sf.append("\ts.error_code AS errorCode,\n");
        sf.append("\tay3.type_desc AS typedesc,");
        sf.append("\tCASE s.speed_status\n");
        sf.append("WHEN '0' THEN\n");
        sf.append("\t'HD待分派'\n");
        sf.append("WHEN '1' THEN\n");
        sf.append("\t'第三方已拒绝'\n");
        sf.append("WHEN '2' THEN\n");
        sf.append("\t'已分派第三方'\n");
        sf.append("WHEN '3' THEN\n");
        sf.append("\t'第三方已分配'\n");
        sf.append("WHEN '4' THEN\n");
        sf.append("\t'区域工程师已分配'\n");
        sf.append("WHEN '5' THEN\n");
        sf.append("\t'工程师已接受'\n");
        sf.append("WHEN '6' THEN\n");
        sf.append("\t'工程师实施中'\n");
        sf.append("WHEN '7' THEN\n");
        sf.append("\t'服务已完成'\n");
        sf.append("END AS speed,");
        sf.append("	l.assign_des AS assign_des,\n");
        sf.append("	t1.`name` AS t1Name,\n");
        sf.append("	t2.`name` AS t2Name,\n");
        sf.append("	IFNULL(\n");
        sf.append("		DATE_FORMAT(\n");
        sf.append("			s.assign_time,\n");
        sf.append("			'%Y-%m-%d %H:%i:%s'\n");
        sf.append("		),\n");
        sf.append("		''\n");
        sf.append("	) AS assignTime,\n");
        sf.append("	s.`level` AS `level`,\n");
        sf.append("	s.`description` AS description,\n");
        sf.append("	s.contacts_phone AS phone,\n");
        sf.append("	s.email AS email,\n");
        sf.append("	s.contacts_name AS `name`,\n");
        sf.append("	s.`zy_cinema_name` AS ciName,\n");
        sf.append("	s.address AS address,\n");
        sf.append("	s.type_intheater_name AS type_intheater_name,\n");
        sf.append("	IFNULL(s.sn,s.sn_server) AS sn,\n");
        sf.append("	s.authentication AS authentication,\n");
        sf.append("	s.HANDLE_DES AS handleDes,\n");
        sf.append("	ay.TYPE_DESC AS type_desc,\n");
        sf.append(" CASE\n" +
                "WHEN TIMESTAMPDIFF(\n" +
                "\tDAY,\n" +
                "\tNOW(),\n" +
                "\teq.repair_end_date\n" +
                ") < 0 THEN\n" +
                "\t'保外'\n" +
                "WHEN TIMESTAMPDIFF(\n" +
                "\tDAY,\n" +
                "\tNOW(),\n" +
                "\teq.repair_end_date\n" +
                ") > 0 THEN\n" +
                "\t'保内'\n" +
                "ELSE\n" +
                "\t''\n" +
                "END AS inReparirDateVal,\n");
        sf.append(" CASE eq.laser_reconstruction_type\n" +
                "WHEN '1' THEN\n" +
                "\t'未光改'\n" +
                "WHEN '2' THEN\n" +
                "\t'光峰光改'\n" +
                "WHEN '3' THEN\n" +
                "\t'巴可光改'\n" +
                "WHEN '4' THEN\n" +
                "\t'其他光改'\n" +
                "END AS laserreconstructiontype,\n");
        sf.append("IF (\n");
        sf.append("	eq.LASER_RECONSTRUCTION = 1,\n");
        sf.append("	'是',\n");
        sf.append("	'否'\n");
        sf.append(") AS laser,\n");
        sf.append(" IFNULL(\n");
        sf.append("	DATE_FORMAT(\n");
        sf.append("		s.order_time,\n");
        sf.append("		'%Y-%m-%d %H:%i:%s'\n");
        sf.append("	),\n");
        sf.append("	''\n");
        sf.append(") AS order_time,\n");
        sf.append("l.memo as memo\n");
        sf.append("FROM\n");
        sf.append("	CBS_SERVICE_ASSIGN_LIST l\n");
        sf.append("LEFT JOIN cbs_service s ON l.service_id = s.id\n");
        sf.append("LEFT JOIN cbs_service_type t1 ON s.service_type_1 = t1.id\n");
        sf.append("LEFT JOIN cbs_service_type t2 ON s.service_type_2 = t2.id\n");
        sf.append("LEFT JOIN zy_contacts c ON c.id = s.zy_contacts_id\n");
        sf.append("LEFT JOIN zy_cinema ci ON ci.id = s.zy_cinema_id\n");
        sf.append("LEFT JOIN zy_equipment_spare eq ON eq.id = s.zy_equipment_spare_id\n");
        sf.append("LEFT JOIN zy_equipment_type ay ON ay.id = eq.type\n");
        sf.append("LEFT JOIN zy_equipment_type ay2 ON ay2.id = ay.parent_id\n");
        sf.append("LEFT JOIN zy_equipment_type ay3 ON ay3.id = ay2.parent_id");
        sf.append(" WHERE\n");
        sf.append("	s.id = '").append(ServiceId).append("' ");
        sf.append("AND assign_touser = '").append(UserId).append("'");
        return this.findForJdbc(sf.toString());
    }

    @Override
    public List<Map<String, Object>> getDetailOfDistrictService(String ServiceId, String UserId) {
        StringBuffer sf = new StringBuffer();
        sf.append("SELECT\n");
        sf.append("s.id as id,\n");
        sf.append("s.`code` AS `code`,\n");
        sf.append("	IFNULL(\n");
        sf.append("		DATE_FORMAT(\n");
        sf.append("			s.assign_time,\n");
        sf.append("			'%Y-%m-%d %H:%i:%s'\n");
        sf.append("		),\n");
        sf.append("		''\n");
        sf.append("	) AS assignTime,\n");
        sf.append("\tIFNULL(\n");
        sf.append("\t\tconcat(\n");
        sf.append("\n");
        sf.append("\t\t\tIF (\n");
        sf.append("\t\t\t\ts.`SPEED_STATUS` = 7,\n");
        sf.append("\t\t\t\tTIMESTAMPDIFF(\n");
        sf.append("\t\t\t\t\tHOUR,\n");
        sf.append("\t\t\t\t\ts.assign_time,\n");
        sf.append("\t\t\t\t\ts.close_time\n");
        sf.append("\t\t\t\t),\n");
        sf.append("\t\t\t\tTIMESTAMPDIFF(HOUR, s.assign_time, NOW())\n");
        sf.append("\t\t\t),\n");
        sf.append("\t\t\t'小时'\n");
        sf.append("\t\t),\n");
        sf.append("\t\t''\n");
        sf.append("\t) AS dealWithTiem,\n");
        sf.append("\ts.error_code AS errorCode,\n");
        sf.append("\tay3.type_desc AS typedesc,");
        sf.append("IFNULL(\n");
        sf.append("\t\tDATE_FORMAT(eq.install_date, '%Y-%m-%d'),\n");
        sf.append("\t\t'无'\n");
        sf.append("\t) AS installDate,");
        sf.append("IFNULL(\n");
        sf.append("\t\tDATE_FORMAT(\n");
        sf.append("\t\t\teq.warranty_begin_date,\n");
        sf.append("\t\t\t'%Y-%m-%d'\n");
        sf.append("\t\t),\n");
        sf.append("\t\t'无'\n");
        sf.append("\t) AS warrantyBeginDate,\n");
        sf.append("\tIFNULL(\n");
        sf.append("\t\tDATE_FORMAT(\n");
        sf.append("\t\t\teq.repair_end_date,\n");
        sf.append("\t\t\t'%Y-%m-%d'\n");
        sf.append("\t\t),\n");
        sf.append("\t\t'无'\n");
        sf.append("\t) AS repairEndDate,");
        sf.append("CASE eq.is_normal\n");
        sf.append("WHEN '1' THEN\n");
        sf.append("\t'\n");
        sf.append("是'\n");
        sf.append("ELSE\n");
        sf.append("\t'否'\n");
        sf.append("END AS isNormal,");
        sf.append("\tCASE s.speed_status\n");
        sf.append("WHEN '0' THEN\n");
        sf.append("\t'HD待分派'\n");
        sf.append("WHEN '1' THEN\n");
        sf.append("\t'第三方已拒绝'\n");
        sf.append("WHEN '2' THEN\n");
        sf.append("\t'已分派第三方'\n");
        sf.append("WHEN '3' THEN\n");
        sf.append("\t'第三方已分配'\n");
        sf.append("WHEN '4' THEN\n");
        sf.append("\t'区域工程师已分配'\n");
        sf.append("WHEN '5' THEN\n");
        sf.append("\t'工程师已接受'\n");
        sf.append("WHEN '6' THEN\n");
        sf.append("\t'工程师实施中'\n");
        sf.append("WHEN '7' THEN\n");
        sf.append("\t'服务已完成'\n");
        sf.append("END AS speed,");
        sf.append(" s.speed_status AS speed_status,");
        sf.append(" CASE eq.laser_reconstruction_type\n" +
                "WHEN '1' THEN\n" +
                "\t'未光改'\n" +
                "WHEN '2' THEN\n" +
                "\t'光峰光改'\n" +
                "WHEN '3' THEN\n" +
                "\t'巴可光改'\n" +
                "WHEN '4' THEN\n" +
                "\t'其他光改'\n" +
                "END AS laserreconstructiontype,\n");
        sf.append("IF (\n");
        sf.append("	eq.LASER_RECONSTRUCTION = 1,\n");
        sf.append("	'是',\n");
        sf.append("	'否'\n");
        sf.append(") AS laser,\n");
        sf.append("	l.assign_des AS assign_des,\n");
        sf.append("	t1.`name` AS t1Name,\n");
        sf.append("	t2.`name` AS t2Name,\n");
        sf.append("	IFNULL(\n");
        sf.append("		DATE_FORMAT(\n");
        sf.append("			s.assign_time,\n");
        sf.append("			'%Y-%m-%d %H:%i:%s'\n");
        sf.append("		),\n");
        sf.append("		''\n");
        sf.append("	) AS assignTime,\n");
        sf.append("	s.`level` AS `level`,\n");
        sf.append("	s.`description` AS description,\n");
        sf.append("	s.`authentication` AS authentication,\n");
        sf.append("	s.HANDLE_DES AS handleDes,\n");
        sf.append("	s.contacts_phone AS phone,\n");
        sf.append("	s.email AS email,\n");
        sf.append("	s.contacts_name AS `name`,\n");
        sf.append("	s.`zy_cinema_name` AS ciName,\n");
        sf.append("	s.address AS address,\n");
        sf.append("	s.type_intheater_name AS type_intheater_name,\n");
        sf.append("	IFNULL(s.sn,s.sn_server) AS sn,\n");
        sf.append("	ay.TYPE_DESC AS type_desc,\n");
        sf.append("CASE\n" +
                "WHEN TIMESTAMPDIFF(\n" +
                "\tDAY,\n" +
                "\tNOW(),\n" +
                "\teq.repair_end_date\n" +
                ") < 0 THEN\n" +
                "\t'保外'\n" +
                "WHEN TIMESTAMPDIFF(\n" +
                "\tDAY,\n" +
                "\tNOW(),\n" +
                "\teq.repair_end_date\n" +
                ") > 0 THEN\n" +
                "\t'保内'\n" +
                "ELSE\n" +
                "\t''\n" +
                "END  AS inReparirDateVal,\n");
        sf.append(" IFNULL(\n");
        sf.append("	DATE_FORMAT(\n");
        sf.append("		s.order_time,\n");
        sf.append("		'%Y-%m-%d %H:%i:%s'\n");
        sf.append("	),\n");
        sf.append("	''\n");
        sf.append(") AS order_time,\n");
        sf.append("l.memo as memo\n");
        sf.append("FROM\n");
        sf.append("	CBS_SERVICE_ASSIGN_LIST l\n");
        sf.append("LEFT JOIN cbs_service s ON l.service_id = s.id\n");
        sf.append("LEFT JOIN cbs_service_type t1 ON s.service_type_1 = t1.id\n");
        sf.append("LEFT JOIN cbs_service_type t2 ON s.service_type_2 = t2.id\n");
        sf.append("LEFT JOIN zy_contacts c ON c.id = s.zy_contacts_id\n");
        sf.append("LEFT JOIN zy_cinema ci ON ci.id = s.zy_cinema_id\n");
        sf.append("LEFT JOIN zy_equipment_spare eq ON eq.id = s.zy_equipment_spare_id\n");
        sf.append("LEFT JOIN zy_equipment_type ay ON ay.id = eq.type\n");
        sf.append("LEFT JOIN zy_equipment_type ay2 ON ay2.id = ay.parent_id\n");
        sf.append("LEFT JOIN zy_equipment_type ay3 ON ay3.id = ay2.parent_id");
        sf.append(" WHERE\n");
        sf.append("	s.id = '").append(ServiceId).append("' ");
        sf.append("AND assign_touser = '").append(UserId).append("'");
        return this.findForJdbc(sf.toString());
    }

    @Override
    public List<Map<String, Object>> getFileLogByServiceId(String ServiceId, String type) {
        StringBuffer sf = new StringBuffer();
        sf.append("SELECT\n");
        sf.append("\tid as id,\n");
        sf.append("  old_file_name AS oldFileName\n");
        sf.append("FROM\n");
        sf.append("\tzy_file_log\n");
        sf.append("WHERE\n");
        sf.append("\tbusiness_type = 'D'\n");
        sf.append("AND business_child_type = '").append(type).append("'");
        sf.append("AND business_key = '").append(ServiceId).append("'");
        return this.findForJdbc(sf.toString());
    }

    @Override
    public List<Map<String, Object>> getNumberByUserId(String userId) {
        StringBuffer sf = new StringBuffer();
        sf.append("SELECT\n");
        sf.append("	count(s.`code`) as num\n");
        sf.append("FROM\n");
        sf.append("	cbs_service s\n");
        sf.append("WHERE\n");
        sf.append("	id IN (\n");
        sf.append("		SELECT\n");
        sf.append("			l.service_id\n");
        sf.append("		FROM\n");
        sf.append("			`cbs_service_assign_list` l\n");
        sf.append("		WHERE\n");
        sf.append("			l.assign_touser = '").append(userId).append("'");
        sf.append("		AND l.activity = 1\n");
        sf.append("		AND l.`status` != 1\n");
        sf.append("		AND l.`status` != 2\n");
        sf.append("		AND l.`status` != 4\n");
        sf.append("	)\n");
        sf.append("AND s. STATUS != 4\n");
        sf.append("AND s. STATUS != 5;");
        return this.findForJdbc(sf.toString());
    }

    /*
     * 添加发货单操作日志
     * description:操作描述
     * businessId:业务id
     * */
    public void addDeliverLog(String description, String businessId, int processStatus) {

        CbsHandleLogEntity log = new CbsHandleLogEntity();
        log.setBusinessId(businessId);
        log.setDescription(description);
        log.setLogType(2);
        log.setProcessStatus(processStatus);
        systemService.save(log);
    }

    /*
     * 自动发创建发货单
     * */
    @Override
    public void autoDoAdd(String id, String realName) throws Exception {
        //根据id查询备件申请单信息
        CbsEquipmentApplyEntity equipmentApply = systemService.getEntity(CbsEquipmentApplyEntity.class, id);
        Integer zt = equipmentApply.getZt();
        String srCode = equipmentApply.getSrCode();
        String serviceId = equipmentApply.getServiceId();
        String address = equipmentApply.getAddress();
        TSTerritory sheng = equipmentApply.getSheng();
        TSTerritory shi = equipmentApply.getShi();
        String userName = equipmentApply.getUserName();
        String phone = equipmentApply.getPhone();
        String cinemaName = equipmentApply.getCinemaName();
        String warehouseId = equipmentApply.getWarehouseId();
        String memo = equipmentApply.getMemo();


        CbsServiceEntity service = systemService.get(CbsServiceEntity.class, serviceId);
        CbsDelinerEntity cbsDeliner = new CbsDelinerEntity();
        //将 申请单id,服务单ID,是否自提,影院名称,所在省,所在市,具体地址,仓库id等信息添加进发货单表
        cbsDeliner.setCreateDate(new Date());
        cbsDeliner.setZt(zt);
        cbsDeliner.setCrCode(service.getCode());
        cbsDeliner.setSoCode(service.getSo());
        cbsDeliner.setSrCode(srCode);
        cbsDeliner.setAddress(address);
        cbsDeliner.setSheng(sheng);
        cbsDeliner.setShi(shi);
        cbsDeliner.setEquipmentApplyId(equipmentApply);
        cbsDeliner.setUserName(userName);
        cbsDeliner.setPhone(phone);
        cbsDeliner.setCinemaName(cinemaName);
        cbsDeliner.setSpaceNumber(equipmentApply.getRecordId());
        cbsDeliner.setCreateDate(new Date());        
        if (warehouseId != null && warehouseId != "") {
            CbsWarehouseEntity ztWarehouse = systemService.get(CbsWarehouseEntity.class, warehouseId);
            cbsDeliner.setWarehouseId(ztWarehouse);
        }

        //根据城市获取推荐物流
        if (shi != null) {
            List<Map<String, Object>> wList = systemService.findForJdbc("SELECT\n" +
                    "\tw.id AS id,\n" +
                    "\tw.`name` AS `name`,w.phone AS phone,w.email AS email,w.user_name AS userName,\n" +
                    "\tw.logistics AS logistics,\n" +
                    "\ts.is_priority AS isPriority,\n" +
                    "\tCASE\n" +
                    "WHEN s.sla_type = '1' THEN\n" +
                    "\t'SL1'\n" +
                    "WHEN s.sla_type = '2' THEN\n" +
                    "\t'SL2'\n" +
                    "WHEN s.sla_type = '3' THEN\n" +
                    "\t'SL3'\n" +
                    "WHEN s.sla_type = '4' THEN\n" +
                    "\t'SL4'\n" +
                    "ELSE\n" +
                    "\t'无'\n" +
                    "END AS slaType\n" +
                    "FROM\n" +
                    "\tcbs_warehouse w\n" +
                    "LEFT JOIN cbs_warehouse_sla s ON w.id = s.cbs_warehouse_id\n" +
                    "WHERE\n" +
                    "\ts.shi = '" + shi.getId() + "'\n" +
                    "AND s.is_priority = '1'\n" +
                    "ORDER BY s.sla_type DESC");
            if (wList != null && wList.size() > 0 && wList.get(0) != null) {
                cbsDeliner.setDeliverLogistics((String) wList.get(0).get("logistics"));
                cbsDeliner.setWlSLA((String) wList.get(0).get("slaType"));
                CbsWarehouseEntity wareHouse = systemService.get(CbsWarehouseEntity.class, (String) wList.get(0).get("id"));
                if (wareHouse != null) {
                    cbsDeliner.setDeliverWarehouseId(wareHouse);
                }
            }
        }
        cbsDeliner.setDeliverType("1");
        cbsDeliner.setStatus("1");
        cbsDeliner.setFhSLA("1");
        String deliverCode = cbsDelinerService.getDelinerCode();
        cbsDeliner.setCode(deliverCode);
        systemService.save(cbsDeliner);

        //根据备件申请单id查询申请备件信息
        List<CbsEquipmentApplyDetailEntity> equipmentApplyDetailList = systemService.findHql("from CbsEquipmentApplyDetailEntity where applyId.id='" + id + "'");
        if (equipmentApplyDetailList.size() > 0 && equipmentApplyDetailList != null) {
            for (CbsEquipmentApplyDetailEntity ead : equipmentApplyDetailList) {
                int totle = ead.getTotal();
                if (totle > 0) {
                    for (int i = 0; i < totle; i++) {
                        CbsDeliverEquipmentEntity cbsDeliverEquipment = new CbsDeliverEquipmentEntity();
                        //设置备件信息
                        cbsDeliverEquipment.setDeliverId(cbsDeliner);
                        cbsDeliverEquipment.setEquipmentApplyDetailId(ead);
                        cbsDeliverEquipment.setApplyLayer(ead.getApplyLayer());
                        if (ead != null) {
                            cbsDeliverEquipment.setTypeDes(ead.getTypeDes());
                            cbsDeliverEquipment.setTypeId(ead.getTypeId());
                        }
                        if (ead != null) {
                            if (ead.getItemId() != null) {
                                cbsDeliverEquipment.setItemId(ead.getItemId());
                                //根据备件号查询是否需要返件
                                if (ead.getItemId().getIsReturn() != null) {
                                    if (ead.getItemId().getIsReturn().equals("1")) {//需要返件
                                        cbsDeliverEquipment.setIsReturn(2);
                                    }
                                    if (ead.getItemId().getIsReturn().equals("2")) {//不需要返件
                                        cbsDeliverEquipment.setIsReturn(1);
                                    }

                                }
                            } else {
                                cbsDeliverEquipment.setIsReturn(2);
                            }
                            cbsDeliverEquipment.setItem(ead.getItem());
                        }
                        cbsDeliverEquipment.setTotal(1);
                        cbsDeliverEquipment.setMemo(ead.getMemo());
                        cbsDeliverEquipment.setApplyReason(equipmentApply.getApplyReason());
                        //保存备件
                        systemService.save(cbsDeliverEquipment);
                    }
                }


            }
        } else {
            CbsDeliverEquipmentEntity cbsDeliverEquipment = new CbsDeliverEquipmentEntity();
            cbsDeliverEquipment.setDeliverId(cbsDeliner);
            systemService.save(cbsDeliverEquipment);
        }
        //记录操作日志
        //获取登录的用户信息
        addDeliverLog(realName + "创建了发货单", cbsDeliner.getId(), 1);

    }

    //当前日期转化为MMdd字符转
    public String getDateStr(Date date) {
        SimpleDateFormat df = new SimpleDateFormat("MMdd");
        String dateStr = df.format(date);
        return dateStr;
    }

    /*
     * 商务服务单详情中的备件申请列表
     * */
    @Override
    public List<Object[]> findEquipmentList(String id) {
        String sql = "SELECT\n" +
                "\t\t\tifnull(ea.record_id, '') AS record_Id,\n" +
                "\t\t\tifnull(ea.apply_reason, '') AS apply_reason,\n" +
                "\t\t\tifnull(d.expecttime, '') AS expecttime,\n" +
                "\t\t\tifnull(d.status, '') AS status,\n" +
                "\t\t\tifnull(d.deliver_logistics, '') AS deliver_logistics,\n" +
                "\t\t\tifnull(\n" +
                "\t\t\t\tGROUP_CONCAT(ead.type_des SEPARATOR '\\\\'),\n" +
                "\t\t\t\t''\n" +
                "\t\t\t) AS typeDes,\n" +
                "\t\t\tifnull(\n" +
                "\t\t\t\tGROUP_CONCAT(ead.item SEPARATOR '\\\\'),\n" +
                "\t\t\t\t''\n" +
                "\t\t\t) AS item,\n" +
                "\t\t\tifnull(\n" +
                "\t\t\t\tGROUP_CONCAT(ead.total SEPARATOR '\\\\'),\n" +
                "\t\t\t\t''\n" +
                "\t\t\t) AS total,\n" +
                "\t\t\tifnull(ea. STATUS, '') AS APPLYSTATUS,\n" +
                "\t\t\tifnull(ea. reasion, '') AS reasion\n" +
                "\t\tFROM\n" +
                "\t\t\tcbs_equipment_apply ea\n" +
                "\t\tLEFT JOIN cbs_equipment_apply_detail ead ON ea.id = ead.apply_id\n" +
                "LEFT JOIN cbs_deliver d ON ea.id=d.equipment_apply_id\n" +
                "\t\tWHERE\n" +
                "\t\t\tea.service_id = '" + id + "'\n" +
                "\t\tGROUP BY\n" +
                "\t\t\tea.id\n" +
                "\n";
        List<Object[]> listbySql = systemService.findListbySql(sql);
        return listbySql;
    }

    @Override
    public AjaxJson findArgEngineerPeople(String planIds, String providerId, String assignTouserId, String assignToroleId, String assignTorole, String reason) {
        AjaxJson ajaxJson = new AjaxJson();
        boolean flag = true;
        TSUser user = ResourceUtil.getSessionUserName();
        ZyEngineerEntity zyEngineerEntitiesUser = null;
        ZyEngineerEntity zyEngineerEntities = null;
        try {
            if (!StringUtil.isNotEmpty(user)) {
                ajaxJson.setSuccess(false);
                ajaxJson.setMsg("获取不到用户信息，请重新登录！");
                flag = false;
            } else if (StringUtil.isNotEmpty(planIds) && StringUtil.isNotEmpty(assignTouserId) && StringUtil.isNotEmpty(assignTorole) && StringUtil.isNotEmpty(providerId) && StringUtil.isNotEmpty(assignToroleId)) { //服务单id
                zyEngineerEntities = systemService.findUniqueByProperty(ZyEngineerEntity.class, "tsBaseUser.id", user.getId());//当前登录工程师
                zyEngineerEntitiesUser = systemService.getEntity(ZyEngineerEntity.class, assignTouserId);  //接单工程师
                String[] planIdsArr = planIds.split(",");
                for (String id : planIdsArr) {
                    CbsServiceEntity cbsServiceEntity = systemService.get(CbsServiceEntity.class, id);
                    if (cbsServiceEntity.getSpeedStatus().equals(StaticValue.qygcsyfp)) { //区域工程师已分配状态下，只能分派给下级，不能分配给同级
                        if (assignTorole.equals(StaticValue.RULE_CODE_AREA_ENGINEER)) {  //区域派单给区域
                            ajaxJson.setSuccess(false);
                            ajaxJson.setMsg("服务单已经分配给工程师，不能再分配给区域工程师！");
                            flag = false;
                        }
                    }
                    if (flag) {
                        List<CbsServiceAssignListEntity> cbsServiceAssignListEntities = systemService.findHql("from CbsServiceAssignListEntity where serviceId.id = ? and activity = ? ", id, 1);
                        if (StringUtil.isNotEmpty(cbsServiceAssignListEntities) && cbsServiceAssignListEntities.size() > 0 && StringUtil.isNotEmpty(cbsServiceAssignListEntities.get(0))) {
                            CbsServiceAssignListEntity cbsServiceAssignListEntity = cbsServiceAssignListEntities.get(0);
                            if (StringUtil.isNotEmpty(cbsServiceAssignListEntity) && StringUtil.isNotEmpty(zyEngineerEntitiesUser) && StringUtil.isNotEmpty(zyEngineerEntities)) {
                                CbsServiceAssignListEntity cbsServiceAssignListEntity1 = new CbsServiceAssignListEntity();
                                cbsServiceAssignListEntity1.setServiceId(cbsServiceEntity);
                                if (assignTorole.equals(StaticValue.RULE_CODE_AREA_ENGINEER)) { //区域工程师派给同级区域工程师
                                    cbsServiceAssignListEntity1.setAssignType(Integer.parseInt(StaticValue.assign_type_3));
                                    cbsServiceAssignListEntity1.setAssignTorole(Integer.parseInt(StaticValue.assign_torole_2)); //被指派人员角色
                                    //老单子，修改状态
                                    cbsServiceAssignListEntity.setStatus(1);//同级已转派，老单子不可见
                                    cbsServiceEntity.setSpeedStatus(StaticValue.dsfyfp); //第三方处理进度为第三方已分配
                                    cbsServiceEntity.setAreaEngineer(assignTouserId); /*接单的工程师*/
                                    cbsServiceEntity.setEngineerTime(new Date());
                                    systemService.saveOrUpdate(cbsServiceEntity);
                                    cbsServiceLogServiceI.addServiceLog(cbsServiceEntity, StaticValue.Close_Service_Log_Type_2, "服务单发生同级转派。" + (StringUtil.isNotEmpty(zyEngineerEntities.getName()) ? "给:" + zyEngineerEntitiesUser.getName() : "") + (StringUtil.isNotEmpty(reason) ? ("备注：" + reason) : ""), null);
                                } else if (assignTorole.equals(StaticValue.RULE_CODE_THIRDPARTY_ENGINEER)) {  //区域派单给工程师
                                    //判断是否是派给自己
                                    if (StringUtil.isNotEmpty(zyEngineerEntities)) {
                                        if (zyEngineerEntities.getId().equals(assignTouserId)) {
                                            //单子派给了自己
                                            cbsServiceAssignListEntity1.setAssignTypeEg(1);
                                        }
                                    }
                                    cbsServiceAssignListEntity1.setAssignType(Integer.parseInt(StaticValue.assign_type_4)); /*派给普通工程师 类型*/
                                    cbsServiceAssignListEntity1.setAssignTorole(Integer.parseInt(StaticValue.assign_torole_3)); /*普通工程师 角色*/
                                    //老单子，修改状态
                                    if (cbsServiceAssignListEntity.getAssignTorole() == 3) { //老单子接单人是工程师
                                        cbsServiceAssignListEntity.setStatus(1);//将上一个派给工程师的单子，至为同级工程师不可见
                                    } else {
                                        cbsServiceAssignListEntity.setStatus(4);
                                    }
                                    cbsServiceEntity.setSpeedStatus(StaticValue.qygcsyfp); //第三方处理进度为区域工程师已分配
                                    cbsServiceEntity.setEngineer(assignTouserId); /*接单工程师*/
                                    cbsServiceEntity.setEngineerTime(new Date());
                                    systemService.saveOrUpdate(cbsServiceEntity);
                                    cbsServiceLogServiceI.addServiceLog(cbsServiceEntity, StaticValue.Close_Service_Log_Type_2, "服务单已分派第三方工程师：" + zyEngineerEntitiesUser.getName() + (StringUtil.isNotEmpty(reason) ? ("。备注：" + reason) : ""), null);
                                }
                                cbsServiceAssignListEntity1.setAssignToroleId(assignToroleId); //接单人的角色的id
                                cbsServiceAssignListEntity1.setAssignDes(cbsServiceAssignListEntity.getAssignDes()); //派单要求
                                cbsServiceAssignListEntity1.setStatus(0);//被派单
                                cbsServiceAssignListEntity1.setActivity(1);//是当前活动用户
                                cbsServiceAssignListEntity1.setServiceProviderId(providerId);  //服务商id
                                cbsServiceAssignListEntity1.setAssignTouser(assignTouserId); //接单人id
                                cbsServiceAssignListEntity1.setMemo(reason); //备注
                                cbsServiceAssignListEntity1.setCreateDate(new Date());
                                cbsServiceAssignListEntity1.setCreateBy(user.getUserName());
                                cbsServiceAssignListEntity1.setCreateName(user.getRealName());
                                systemService.saveOrUpdate(cbsServiceAssignListEntity1);

                                cbsServiceAssignListEntity.setActivity(0);//不是当前活动用户
                                systemService.saveOrUpdate(cbsServiceAssignListEntity);

                                /*短信*/
                                String level = cbsServiceEntity.getLevel();
                                String type = "";
                                if (StringUtil.isNotEmpty(cbsServiceEntity.getZyEquipmentSpareServiceId())) {
                                    type = cbsServiceEntity.getZyEquipmentSpareServiceId().getType().getTypeDesc();
                                } else if (StringUtil.isNotEmpty(cbsServiceEntity.getZyEquipmentSpareId())) {
                                    type = cbsServiceEntity.getZyEquipmentSpareId().getType().getTypeDesc();
                                }
                                String serviceType = cbsServiceEntity.getServiceType2().getName();
                                String code = cbsServiceEntity.getCode();
                                String zyCinemaName = cbsServiceEntity.getZyCinemaName();
                                SimpleDateFormat sdf = new SimpleDateFormat("MMdd");
                                TSDepart depart = systemService.findUniqueByProperty(TSDepart.class, "orgCode", cbsServiceEntity.getServiceProviderCode());
                                /*邮件*/
                                ZyEmailEntity eMail = new ZyEmailEntity();
                                String s = "巴可派单：" + level + "-" + serviceType + "-" + code + "-" + zyCinemaName + "-" + sdf.format(new Date()) + "";
                                eMail.setEmailSubject(s);
                                eMail.setEmailReceiver(zyEngineerEntitiesUser.getEmail());
                                Map<String, Object> paraMap1 = new HashMap<String, Object>();
                                paraMap1.put("contacterName", StringUtil.isNotEmpty(depart) ? depart.getShortname() : "");//服务单名称
                                paraMap1.put("type", type);
                                paraMap1.put("sn", StringUtil.isNotEmpty(cbsServiceEntity.getSn()) ? cbsServiceEntity.getSn() : cbsServiceEntity.getSnServer());
                                paraMap1.put("description", cbsServiceEntity.getDescription());
                                paraMap1.put("level", level);
                                paraMap1.put("authentication", cbsServiceEntity.getAuthentication());
                                paraMap1.put("zyCinemaName", cbsServiceEntity.getZyCinemaName());
                                paraMap1.put("zyContactsName", cbsServiceEntity.getContactsName());
                                paraMap1.put("phone", cbsServiceEntity.getContactsPhone());
                                if (StringUtil.isNotEmpty(zyEngineerEntitiesUser.getEmail())) {
                                    SendMailUtil.sendFtlMail(eMail, "mail_send_single_service_business.ftl", paraMap1, systemService);
                                }
                                /*APP消息*/
                                CbsAppMessageEntity appMessage = new CbsAppMessageEntity();
                                String content = "您收到1个新的任务，具体内容请查看http://cbs.cfg-barco.com或登陆巴可客服App。";
                                appMessage.setContent(content);
                                appMessage.setSender(user.getId());
                                TSBaseUser baseUser = systemService.get(TSBaseUser.class, zyEngineerEntitiesUser.getTsBaseUser().getId());
                                appMessage.setReciver(baseUser.getId());
                                appMessage.setState(StaticValue.DIC_APP_MESSAGE_STATE_UNREAD);
                                systemService.save(appMessage);
                                systemService.addLog("新增APP消息,接受方:" + baseUser.getId() + ",内容:" + content, Globals.Log_Leavel_INFO, Globals.log_type_SEND_EMAIL);
                            }
                        }
                    }
                }
            } else {
                ajaxJson.setSuccess(false);
                ajaxJson.setMsg("获取不到服务单信息，请联系管理员！");
                flag = false;
            }
        } catch (Exception e) {
            flag = false;
            ajaxJson.setSuccess(false);
            ajaxJson.setMsg("事务出现异常,回滚事务！");
            e.printStackTrace();
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();//此处采用手动回滚
        }
        return ajaxJson;
    }

    @Override
    public void queryFacilitatorData(HttpServletRequest request, HttpServletResponse response, DataGrid dataGrid) {

        Map param = new HashMap<>();
        String speedstatus1 = request.getParameter("speedstatus1");
        TSUser user = ResourceUtil.getSessionUserName();
        param.put("userId", user.getId());

        for (Object key : request.getParameterMap().keySet()) {
            if ((String[]) request.getParameterMap().get(key) != null) {
                for (int i = 0; i < ((String[]) request.getParameterMap().get(key)).length; i++) {
                    if (((String[]) request.getParameterMap().get(key))[i] != null) {
                        param.put(key, ((String[]) request.getParameterMap().get(key))[i].trim());
                    }
                }
            }
        }

        if (!StringUtil.isEmpty(oConvertUtils.getString(param.get("assignTime_begin")))) {
            param.put("assignTime_begin", oConvertUtils.getString(param.get("assignTime_begin")) + " 00:00:01");
        }
        if (!StringUtil.isEmpty(oConvertUtils.getString(param.get("assignTime_end")))) {
            param.put("assignTime_end", oConvertUtils.getString(param.get("assignTime_end")) + " 23:59:59");
        }

        if (!StringUtil.isEmpty(oConvertUtils.getString(param.get("closeTime_begin")))) {
            param.put("closeTime_begin", oConvertUtils.getString(param.get("closeTime_begin")) + " 00:00:01");
        }
        if (!StringUtil.isEmpty(oConvertUtils.getString(param.get("closeTime_end")))) {
            param.put("closeTime_end", oConvertUtils.getString(param.get("closeTime_end")) + " 23:59:59");
        }
        if (StringUtil.isNotEmpty(speedstatus1)) {
            List<String> speedstatusList = new ArrayList<>();
            String[]   speedstatus1Split = speedstatus1.split(",");
            for (int i = 0; i < speedstatus1Split.length; i++) {
                if ("".equals(speedstatus1Split[i])) {
                    continue;
                }
                speedstatusList.add(speedstatus1Split[i]);
            }
            param.put("speedstatusList", speedstatusList);
        }

//        System.out.println("商务列表查询的param=" + com.alibaba.fastjson.JSONObject.toJSONString(param));
        wiseCoreService.selectDataGrid("selectSWserviceDatagrid", param, dataGrid);
        /*-- upadate by x on 20190801 尽量不连表查询多余字段，采用翻译的形式 --*/
        //查询 关联所需要的 省市、工程师、服务类型
        List<Map<String,Object>> regioniMapList = systemService.findForJdbc("select id, territoryname from t_s_territory");
        List<Map<String,Object>> serviceTypeMapList = systemService.findForJdbc("select id, name from cbs_service_type");
        List<Map<String,Object>> deliverMapList = systemService.findForJdbc("select IFNULL(GROUP_CONCAT(expecttime SEPARATOR '\\\\'),'') AS expecttime, cr_code from CBS_DELIVER group by cr_code");

        Map<String, Map> regionMap = StringUtil.listToMapWithKey(regioniMapList, "id");
        Map<String, Map> serviceTypeMap = StringUtil.listToMapWithKey(serviceTypeMapList, "id");
        Map<String, Map> deliverMap = StringUtil.listToMapWithKey(deliverMapList, "cr_code");

        Iterator it = dataGrid.getResults().iterator();
        while(it.hasNext()){
            /**
             *
             cbs.PROVINCE,
             cbs.city,
             cbs.engineer_id,
             cbs.service_type_2
             */
            //替换、翻译数据库字段供页面展示
            Map<String, Object> m = (Map<String, Object>)it.next();
            if (StringUtil.isNotEmpty(m.get("provinces"))) {
                m.put("provinces", regionMap.get(m.get("provinces").toString()).get("territoryname"));
            }
            if (StringUtil.isNotEmpty(m.get("city"))) {
                m.put("city", regionMap.get(m.get("city").toString()).get("territoryname"));
            }
            if (StringUtil.isNotEmpty(m.get("servicetype"))) {
                m.put("servicetype", serviceTypeMap.get(m.get("servicetype").toString()).get("name"));
            }
            if (StringUtil.isNotEmpty(m.get("code")) && null != deliverMap) {
            	if (deliverMap.get(m.get("code").toString()) != null) {
            		m.put("expecttime", deliverMap.get(m.get("code").toString()).get("expecttime"));
            	}
            }
        }
    }

    /**
     * 替换sql中的变量
     *
     * @param sql
     * @return
     */
    public String replaceVal(String sql, CbsServiceAssignListEntity t) {
        sql = sql.replace("#{id}", String.valueOf(t.getId()));
        sql = sql.replace("#{service_id}", String.valueOf(t.getServiceId()));
        sql = sql.replace("#{assign_type}", String.valueOf(t.getAssignType()));
        sql = sql.replace("#{service_provider_id}", String.valueOf(t.getServiceProviderId()));
        sql = sql.replace("#{turn_user_id}", String.valueOf(t.getTurnUserId()));
        sql = sql.replace("#{assign_des}", String.valueOf(t.getAssignDes()));
        sql = sql.replace("#{assign_touser}", String.valueOf(t.getAssignTouser()));
        sql = sql.replace("#{assign_torole}", String.valueOf(t.getAssignTorole()));
        sql = sql.replace("#{assign_torole_id}", String.valueOf(t.getAssignToroleId()));
        sql = sql.replace("#{activity}", String.valueOf(t.getActivity()));
        sql = sql.replace("#{status}", String.valueOf(t.getStatus()));
        sql = sql.replace("#{operation_des}", String.valueOf(t.getOperationDes()));
        sql = sql.replace("#{memo}", String.valueOf(t.getMemo()));
        sql = sql.replace("#{create_name}", String.valueOf(t.getCreateName()));
        sql = sql.replace("#{create_by}", String.valueOf(t.getCreateBy()));
        sql = sql.replace("#{create_date}", String.valueOf(t.getCreateDate()));
        sql = sql.replace("#{update_name}", String.valueOf(t.getUpdateName()));
        sql = sql.replace("#{update_by}", String.valueOf(t.getUpdateBy()));
        sql = sql.replace("#{update_date}", String.valueOf(t.getUpdateDate()));
        sql = sql.replace("#{UUID}", UUID.randomUUID().toString());
        return sql;
    }


}